package com.mgy.common.excel;

import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.DocumentFactoryHelper;
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.charset.Charset;
import java.text.ParseException;
import java.util.Date;

/**
 * 用户模式导出excel
 *
 * @author maguoyong
 * @since 2018/01/24
 */
public class ExcelUtil {

    /**
     * web导出excel，设置excel样式
     *
     * @param dtSource     数据源
     * @param fileName     导出文件的文件名字
     * @param templatePath excel模板的绝对路径
     * @param request      request
     * @param response     response
     * @throws IOException    IOException
     * @throws ParseException ParseException
     */
    public static void exportByWeb(DataTable dtSource, String fileName, String templatePath, HttpServletRequest request, HttpServletResponse response) throws IOException, ParseException {
        File file = new File(templatePath);
        if (!file.exists() || !file.isFile()) {
            throw new IOException("文件不存在：" + templatePath);
        }
        InputStream inputStream = new FileInputStream(file);
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        workbook = dataTableToWorkbook(workbook, dtSource);

        setOutputInfo(fileName, request, response);
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.close();
    }

    public static void setOutputInfo(String fileName, HttpServletRequest request, HttpServletResponse response) throws UnsupportedEncodingException {
        // IE浏览器下载中文文件名字乱码问题
        String userAgent = request.getHeader("User-Agent").toLowerCase();
        if (userAgent.contains("msie") || userAgent.contains("like gecko")) {
            fileName = URLEncoder.encode(fileName, "utf-8");
        } else {
            fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
        }

        response.setContentType("application/ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Type", "text/html; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
    }


    private static HSSFWorkbook dataTableToWorkbook(HSSFWorkbook workbook, DataTable dtSource) throws ParseException {
        if (workbook == null) {
            workbook = new HSSFWorkbook();
        }
        int sheetCount = 1;
        /**
         * 工作簿
         */
        HSSFSheet sheet = null;
        // 右击文件 属性信息
        DocumentSummaryInformation dsi = workbook.getDocumentSummaryInformation();
        //公司
        dsi.setCompany("无");
        SummaryInformation si = workbook.getSummaryInformation();
        //填加xls文件作者信息
        si.setAuthor("mgy");
        si.setApplicationName("无");
        //填加xls文件最后保存者信息
        si.setLastAuthor("mgy");
        si.setComments("无");
        //填加xls文件标题信息
        si.setTitle("无");
        si.setSubject("无");
        si.setCreateDateTime(new Date());

        //时间样式
        HSSFCellStyle dateStyle = workbook.createCellStyle();
        HSSFDataFormat format = workbook.createDataFormat();
        dateStyle.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm"));

        //列头样式
        HSSFCellStyle columnStyle = workbook.createCellStyle();
        HSSFFont columnFont = workbook.createFont();

        //内容样式
        HSSFCellStyle contentStyle = workbook.createCellStyle();
        HSSFFont contentFont = workbook.createFont();

        //取得列宽
        int[] arrColWidth = new int[dtSource.getColumns().size()];
        //计算列头的宽度
        for (DataColumn item : dtSource.getColumns()) {
            arrColWidth[item.getOrdinal()] = item.getColumnName().getBytes(Charset.forName("UTF-8")).length;
        }
        //取单元格的最大宽度
        for (int i = 0; i < dtSource.getRows().size(); i++) {
            for (int j = 0; j < dtSource.getColumns().size(); j++) {
                int intTemp = (dtSource.getValue(i, j) != null ? dtSource.getValue(i, j) : "").toString().getBytes(Charset.forName("UTF-8")).length;
                if (intTemp > arrColWidth[j]) {
                    arrColWidth[j] = intTemp;
                }
            }
        }
        int rowIndex = 0;
        //如果没有内容只导出列头
        if (dtSource.getRows().size() == 0) {
            sheet = workbook.getSheet("Sheet" + (sheetCount++));
            //设置表头内容
            if (dtSource.getHeader() != null && dtSource.getHeader().trim().length() > 0) {
                //表头样式
                setHeader(sheet, rowIndex, dtSource, workbook.createCellStyle(), workbook.createFont());
                rowIndex++;
            }
            //设置列头
            setColumnHeader(sheet, rowIndex, dtSource, columnStyle, columnFont, arrColWidth);
            rowIndex++;
            return workbook;
        }


        for (DataRow row : dtSource.getRows()) {
            // 获取新sheet
            if (rowIndex % 65535 == 0) {
                sheet = workbook.getSheet("Sheet" + (sheetCount++));
                rowIndex = 0;
                //设置表头内容
                if (dtSource.getHeader() != null && dtSource.getHeader().trim().length() > 0) {
                    //表头样式
                    setHeader(sheet, rowIndex, dtSource, workbook.createCellStyle(), workbook.createFont());
                    rowIndex++;
                }
                //设置列头
                setColumnHeader(sheet, rowIndex, dtSource, columnStyle, columnFont, arrColWidth);
                rowIndex++;
            }

            HSSFRow excelRow = sheet.createRow(rowIndex);
            //设置行高
            if (dtSource.getContentStyle().getHeight() != null) {
                excelRow.setHeight(dtSource.getContentStyle().getHeight());
            }
            //单元格
            HSSFCell cell;
            //内容样式
            setCustomCellStyle(contentStyle, contentFont, dtSource.getContentStyle());
            //合并行
            if (row.getMerge()) {
                StringBuffer sb = new StringBuffer();
                for (DataColumn column : dtSource.getColumns()) {
                    sb.append(row.getValue(column.getOrdinal()));
                }
                cell = excelRow.createCell(0);
                cell.setCellValue(sb.toString());
                cell.setCellStyle(contentStyle);
                sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, dtSource.getColumns().size() - 1));
            } else {
                for (DataColumn column : dtSource.getColumns()) {
                    cell = excelRow.createCell(column.getOrdinal());
                    //设置单元格样式
                    cell.setCellStyle(contentStyle);
                    //单元格的值
                    Object cellValue = row.getValue(column.getOrdinal());
                    //根据单元格数据类型转换对应的显示样式
                    convertValueStyle(column.getDataType(), cellValue, cell, dateStyle);
                }
            }
            rowIndex++;
        }

        return workbook;
    }

    /**
     * 根据单元格数据类型转换对应的显示样式
     *
     * @param dataType  数据类型
     * @param cellValue 单元格的值
     * @param cell      单元格
     * @param dateStyle 数据样式
     */
    public static void convertValueStyle(Class dataType, Object cellValue, Cell cell, CellStyle dateStyle) {
        if (dataType == String.class) {
            cell.setCellValue(cellValue != null ? cellValue.toString() : "");
        } else if (dataType == Date.class) {
            if (cellValue != null) {
                cell.setCellValue((Date) cellValue);
            } else {
                cell.setCellValue("");
            }
            //格式化显示
            cell.setCellStyle(dateStyle);
        } else if (dataType == Boolean.class) {
            if (cellValue != null) {
                cell.setCellValue((Boolean) cellValue);
            } else {
                cell.setCellValue("");
            }
        } else if (dataType == Short.class || dataType == Integer.class || dataType == Long.class
                || dataType == Byte.class) {
            if (cellValue != null) {
                cell.setCellValue(cellValue.toString());
            } else {
                cell.setCellValue("");
            }
        } else if (dataType == Float.class || dataType == Double.class) {
            if (cellValue != null) {
                cell.setCellValue((Double) cellValue);
            } else {
                cell.setCellValue("");
            }
        } else {
            cell.setCellValue("");
        }
    }

    /**
     * 设置列头
     *
     * @param sheet       sheet
     * @param rowIndex    行号
     * @param dtSource    数据源
     * @param columnStyle 列样式
     * @param columnFont  列字体
     * @param arrColWidth 列宽
     */
    private static void setColumnHeader(HSSFSheet sheet, Integer rowIndex, DataTable dtSource, HSSFCellStyle columnStyle, HSSFFont columnFont, int[] arrColWidth) {
        //设置列头
        HSSFRow excelRow = sheet.createRow(rowIndex);
        //设置行高
        if (dtSource.getColumnStyle().getHeight() != null) {
            excelRow.setHeight(dtSource.getColumnStyle().getHeight());
        }
        for (DataColumn column : dtSource.getColumns()) {
            HSSFCell columnCell = excelRow.createCell(column.getOrdinal());
            //设置单元格样式
            setCustomCellStyle(columnStyle, columnFont, dtSource.getColumnStyle());
            columnCell.setCellStyle(columnStyle);
            columnCell.setCellValue(column.getColumnName());

            //设置列宽
            if (arrColWidth[column.getOrdinal()] < 100) {
                sheet.setColumnWidth(column.getOrdinal(), (arrColWidth[column.getOrdinal()] + 1) * 256);
            } else {
                sheet.setColumnWidth(column.getOrdinal(), 100 * 256);
            }
        }
    }

    /**
     * 设置表头
     *
     * @param sheet       sheet
     * @param rowIndex    行号
     * @param dtSource    数据源
     * @param headerStyle 表头样式
     * @param headerFont  表头字体
     */
    private static void setHeader(HSSFSheet sheet, Integer rowIndex, DataTable dtSource, HSSFCellStyle headerStyle, HSSFFont headerFont) {
        //设置单元格样式
        setCustomCellStyle(headerStyle, headerFont, dtSource.getHeaderStyle());
        //设置列头
        HSSFRow excelRow = sheet.createRow(rowIndex);
        HSSFCell headerCell = excelRow.createCell(0);
        headerCell.setCellValue(dtSource.getHeader());
        headerCell.setCellStyle(headerStyle);
        //设置行高
        if (dtSource.getHeaderStyle().getHeight() != null) {
            excelRow.setHeight(dtSource.getHeaderStyle().getHeight());
        }
        sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, dtSource.getColumns().size() - 1));
    }

    /**
     * 单元格样式设置
     *
     * @param style      单元格样式对象
     * @param font       字体对象
     * @param excelStyle 列
     * @return
     */
    public static void setCustomCellStyle(CellStyle style, Font font, ExcelStyle excelStyle) {
        //字体位置
        if (excelStyle.getPosition() != null) {
            style.setAlignment(excelStyle.getPosition());
        }

        //字体大小
        if (excelStyle.getFontSize() != null) {
            font.setFontHeightInPoints(excelStyle.getFontSize());
        }
        //字体粗细
        if (excelStyle.getFontWeight() != null) {
            font.setBoldweight(excelStyle.getFontWeight());
        }
        //字体颜色
        if (excelStyle.getFontColor() != null) {
            font.setColor(excelStyle.getFontColor());
        }
        //字体名称
        if (excelStyle.getFontName() != null && excelStyle.getFontName().length() > 0) {
            font.setFontName(excelStyle.getFontName());
        }


        //背景颜色设置
        if (excelStyle.getBgColor() != null) {
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            //设置背景色
            style.setFillForegroundColor(excelStyle.getBgColor());
        }
        //单元格边框粗细设置
        if (excelStyle.getBorderStyle() != null) {
            style.setBorderLeft(excelStyle.getBorderStyle());
            style.setBorderRight(excelStyle.getBorderStyle());
            style.setBorderTop(excelStyle.getBorderStyle());
            style.setBorderBottom(excelStyle.getBorderStyle());
        }
        //边框颜色
        if (excelStyle.getBorderColor() != null) {
            style.setLeftBorderColor(excelStyle.getBorderColor());
            style.setRightBorderColor(excelStyle.getBorderColor());
            style.setTopBorderColor(excelStyle.getBorderColor());
            style.setBottomBorderColor(excelStyle.getBorderColor());
        }
        //字体
        style.setFont(font);
        //超过宽度自动换行
        style.setWrapText(true);
    }

    /**
     * 是否是excel文件格式
     *
     * @param fileName 文件名
     */
    public static boolean isExcel(String fileName) {
        if (fileName == null || fileName.length() == 0) {
            return false;
        }
        return fileName.endsWith(".xlsx") || fileName.endsWith(".xls");
    }

    /**
     * 是否为excel2003及以下版本
     * org.apache.poi.ss.usermodel.WorkbookFactory.create方法中有判断excel版本方法
     *
     * @param inputStream 文件流
     */
    public static boolean isExcel2003(InputStream inputStream) throws IOException {
        if (!inputStream.markSupported()) {
            inputStream = new PushbackInputStream(inputStream, 8);
        }
        byte[] header8 = IOUtils.peekFirst8Bytes(inputStream);
        if (NPOIFSFileSystem.hasPOIFSHeader(header8)) {
            return true;
        }
        return false;
    }

    /**
     * 是否为excel2007及以上版本
     * 测试发现需要转换为BufferedInputStream才能正确判断出来
     *
     * @param inputStream 文件流
     */
    public static boolean isExcel2007(InputStream inputStream) throws IOException {
        if (!inputStream.markSupported()) {
            inputStream = new PushbackInputStream(inputStream, 8);
        }
        if (DocumentFactoryHelper.hasOOXMLHeader(inputStream)) {
            return true;
        }
        return false;
    }


    /**
     * 根据文件扩展名判断是否为excel2003及以下版本
     *
     * @param fileName 文件名称
     */
    public static boolean isExcel2003(String fileName) {
        if (fileName == null || fileName.trim().length() == 0) {
            throw new IllegalArgumentException("fileName不能为空");
        }
        if (fileName.endsWith(".xls")) {
            return true;
        }
        return false;
    }
}
